﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using Webdiyer.WebControls.Mvc;
using MinistryStandardPlatformWeb.Models;
using MinistryStandardPlatformEntity;
using MinistryStandardPlatformWeb.Controllers.RealTime;

namespace MinistryStandardPlatformWeb.Controllers.Reports
{
    [MenuAuthorized("43")]
    public class MileageStatisticsController : Controller
    {
        //
        // GET: /MileageStatistics/
        //里程统计
        private MSPGpsDataContext context = new MSPGpsDataContext();
        private MSPDataContext context1=new MSPDataContext();

        public ActionResult Index(string startDate, string endDate,string vehicles)
        {
            ViewBag.StartDate = startDate;
            ViewBag.EndDate = endDate;
            ViewBag.Vehicles = vehicles;
            return View();
        }

        public ActionResult Data(string startDate, string endDate, string vehicles,int pageIndex=1)
        {
            if (Session["SearchData"] != null)
            {
                return PartialView((Session["SearchData"] as IEnumerable<MileageStatistics>).ToPagedList(pageIndex, CommonDictionary.SmallerPageSize));
            }
            //得到要查询的车辆id的list
            List<string> vehiclesList = vehicles.Split(';').ToList();

            string endDateStr = Convert.ToDateTime(endDate).AddDays(1).ToString(CommonDictionary.DateStrFormat);

            //得到时间段内最早的里程
            var startMileage = from b in context.GpsBasic
                               where vehiclesList.Contains(b.simId) &&
                               string.Compare(b.recordtime, startDate) >= 0 &&
                               string.Compare(b.recordtime, endDateStr) <= 0
                               group b by b.vehicleId into bg
                               let first = bg.Min(t => t.recordtime)
                               from r in bg
                               where
                               r.recordtime == first
                               select new
                               {
                                   Sim = r.simId,
                                   VehicleId = r.vehicleId,
                                   Mileage = r.mileage==-1?r.gpsmileage:r.mileage,
                                   StatisticsTime=r.recordtime
                               };

            //得到时间段内最近的里程
            var endMileage=from b in context.GpsBasic
                           where vehiclesList.Contains(b.simId) &&
                           string.Compare(b.recordtime, startDate) > 0 &&
                           string.Compare(b.recordtime, endDateStr) < 0
                           group b by b.vehicleId into bg
                           let last = bg.Max(t => t.recordtime)
                           from r in bg
                           where r.recordtime == last
                           select new
                           {
                               Sim = r.simId,
                               VehicleId = r.vehicleId,
                               Mileage = r.mileage == -1 ? r.gpsmileage : r.mileage,
                               StatisticsTime=r.recordtime
                           };

            List<string> paramaters = new List<string>();
            for (int i = 0; i < vehiclesList.Count; i++)
            {
                paramaters.Add(vehiclesList[i].ToVarcharSqlParam());
            }
            paramaters.Add(startDate.ToVarcharSqlParam());
            paramaters.Add(endDateStr.ToVarcharSqlParam());
            paramaters.Add("-1");

            string startMileageJsonStr = RealTimeGet.getReportFromGeoBase(CommonFunctions.GetLoginUser().UserId, CommonFunctions.GetSQL(startMileage,paramaters), startDate, endDate,"0");
            string endMileageJsonStr = RealTimeGet.getReportFromGeoBase(CommonFunctions.GetLoginUser().UserId, CommonFunctions.GetSQL(endMileage, paramaters), startDate, endDate,"0");

            List<MileageStatistics> startMileages = CommonFunctions.JsonStrToObject<List<MileageStatistics>>(startMileageJsonStr);
            List<MileageStatistics> endMileages = CommonFunctions.JsonStrToObject<List<MileageStatistics>>(endMileageJsonStr);
            

            //去掉由于分库查询而重复的数据
            var v1 = from b in startMileages
                     group b by b.VehicleId into bg
                     let first = bg.Min(t => t.StatisticsTime)
                     from r in bg
                     where
                     r.StatisticsTime == first
                     select new MileageStatistics()
                     {
                         Sim = r.Sim,
                         VehicleId = r.VehicleId,
                         Mileage = r.Mileage,
                         StatisticsTime = r.StatisticsTime

                     };
            startMileages = v1.ToList();

            var v2 = from b in endMileages
                     group b by b.VehicleId into bg
                     let last = bg.Max(t => t.StatisticsTime)
                     from r in bg
                     where
                     r.StatisticsTime == last
                     select new MileageStatistics()
                     {
                         Sim = r.Sim,
                         VehicleId = r.VehicleId,
                         Mileage = r.Mileage,
                         StatisticsTime = r.StatisticsTime

                     };
            endMileages = v2.ToList();

            //获取车辆的所属单位
            var unit = from v in context1.InfoVehicle
                       from u in context1.InfoUnit
                       where vehiclesList.Contains(v.SIM) &&
                       v.ParentUnitId == u.UNITID
                       select new MileageStatistics()
                       {
                           VehicleId = v.VehicleId,
                           ParentUnitName = u.UNITNAME,
                           Sim=v.SIM,
                           StatisticsTime = startDate + "到" + endDate
                       };
            List<MileageStatistics> unitName = unit.ToList();
            

            //最后的里程减去最早的里程
            var mileages = from m1 in endMileages
                           from m2 in startMileages
                           where m1.VehicleId == m2.VehicleId
                           select new MileageStatistics()
                           {
                               Sim = m1.Sim,
                               VehicleId = m1.VehicleId,
                               Mileage = (Convert.ToDouble(m1.Mileage) - Convert.ToDouble(m2.Mileage)).ToString("f3"),
                               StatisticsTime = startDate + "到" + endDate
                           };
            List<MileageStatistics> mileagesList=mileages.ToList();

            mileages = from n in unitName
                       join m in mileagesList
                       on n.VehicleId equals m.VehicleId
                       into nm
                       from nmd in nm.DefaultIfEmpty()
                       select new MileageStatistics()
                       {
                           Sim = n.Sim,
                           VehicleId = n.VehicleId,
                           Mileage = nmd==null?"0.000":nmd.Mileage,
                           StatisticsTime = startDate + "到" + endDate,
                           ParentUnitName=n.ParentUnitName
                       };
            Session["SearchData"] = mileages;

            PagedList<MileageStatistics> pl =mileages.ToPagedList<MileageStatistics>(pageIndex, CommonDictionary.SmallerPageSize);
            //PagedList<MileageStatistics> r = new PagedList<MileageStatistics>(list, 1, 1);
            return PartialView(pl);
        }

        public ActionResult MileageDetail(string sim, string startDate, string endDate)
        {
            string endDateStr = Convert.ToDateTime(endDate).AddDays(1).ToString(CommonDictionary.DateStrFormat);

            //得到每天的最早时刻的里程
            var startMileage = from b in context.GpsBasic
                               where b.simId==sim &&
                               string.Compare(b.recordtime, startDate) > 0 &&
                               string.Compare(b.recordtime, endDateStr) < 0
                               group b by b.recordtime.Substring(0,CommonDictionary.DateStrFormat.Length) into bg
                               let first = bg.Min(t => t.recordtime)
                               from r in bg
                               where
                               r.recordtime == first
                               select new
                               {
                                   Sim = r.simId,
                                   VehicleId = r.vehicleId,
                                   Mileage = r.mileage == -1 ? r.gpsmileage : r.mileage,
                                   StatisticsTime = r.recordtime.Substring(0, CommonDictionary.DateStrFormat.Length)
                               };

            //得到每天最后时刻的里程
            var endMileage = from b in context.GpsBasic
                             where b.simId == sim &&
                             string.Compare(b.recordtime, startDate) > 0 &&
                             string.Compare(b.recordtime, endDateStr) < 0
                             group b by b.recordtime.Substring(0, CommonDictionary.DateStrFormat.Length) into bg
                             let last = bg.Max(t => t.recordtime)
                             from r in bg
                             where r.recordtime == last
                             select new
                             {
                                 Sim = r.simId,
                                 VehicleId = r.vehicleId,
                                 Mileage = r.mileage == -1 ? r.gpsmileage : r.mileage,
                                 StatisticsTime = r.recordtime.Substring(0, CommonDictionary.DateStrFormat.Length)
                             };

            List<string> paramaters = new List<string>();
            paramaters.Add("0");
            paramaters.Add(CommonDictionary.DateStrFormat.Length.ToString());
            paramaters.Add(sim.ToVarcharSqlParam());
            paramaters.Add(startDate.ToVarcharSqlParam());
            paramaters.Add(endDateStr.ToVarcharSqlParam());
            paramaters.Add("0");
            paramaters.Add(CommonDictionary.DateStrFormat.Length.ToString());
            paramaters.Add("-1");
            paramaters.Add("0");
            paramaters.Add(CommonDictionary.DateStrFormat.Length.ToString());

            string startMileageJsonStr = RealTimeGet.getReportFromGeoBase(CommonFunctions.GetLoginUser().UserId, CommonFunctions.GetSQL(startMileage, paramaters), startDate, endDate,"0");
            string endMileageJsonStr = RealTimeGet.getReportFromGeoBase(CommonFunctions.GetLoginUser().UserId, CommonFunctions.GetSQL(endMileage, paramaters), startDate, endDate, "0");

            List<MileageStatistics> startMileages = CommonFunctions.JsonStrToObject<List<MileageStatistics>>(startMileageJsonStr);
            List<MileageStatistics> endMileages = CommonFunctions.JsonStrToObject<List<MileageStatistics>>(endMileageJsonStr);

            //最后的里程减去最早的里程
            var mileages = from m1 in endMileages
                           from m2 in startMileages
                           where m1.StatisticsTime == m2.StatisticsTime
                           orderby m1.StatisticsTime
                           select new MileageStatistics()
                           {
                               Sim = m1.Sim,
                               VehicleId = m1.VehicleId,
                               Mileage = (Convert.ToDouble(m1.Mileage) - Convert.ToDouble(m2.Mileage)).ToString("f3"),
                               StatisticsTime = m1.StatisticsTime
                           };
            List<MileageStatistics> mlist = mileages.ToList<MileageStatistics>();

            //获取车辆的所属单位
            var unit = from v in context1.InfoVehicle
                       from u in context1.InfoUnit
                       where v.SIM == sim &&
                       v.ParentUnitId == u.UNITID
                       select new MileageStatistics()
                       {
                           VehicleId = v.VehicleId,
                           ParentUnitName = u.UNITNAME,
                           Sim = v.SIM,
                       };
            string unitName = unit.ToList()[0].ParentUnitName;
            string vehicleId = unit.ToList()[0].VehicleId;
            DateTime t1 = Convert.ToDateTime(startDate);
            DateTime t2 = Convert.ToDateTime(endDate);

            //构造每天组成的list，因为查出来的数据如果没有某天的数据那么这天的里程要显示为0
            List<MileageStatistics> days = new List<MileageStatistics>();
            for (DateTime t = t1; t <= t2; t=t.AddDays(1))
            {
                days.Add(new MileageStatistics()
                {
                    StatisticsTime=t.ToString(CommonDictionary.DateStrFormat)
                });
            }

            var dayMile = from n in days
                          join m in mlist
                       on n.StatisticsTime equals m.StatisticsTime
                       into nm
                       from nmd in nm.DefaultIfEmpty()
                       select new MileageStatistics()
                       {
                           Sim = sim,
                           VehicleId = vehicleId,
                           Mileage = nmd == null ? "0.000" : nmd.Mileage,
                           StatisticsTime = n.StatisticsTime,
                           ParentUnitName = unitName
                       };

            //PagedList<MileageStatistics> r = new PagedList<MileageStatistics>(list, 1, 1);
            return PartialView(dayMile.ToList());
        }

        public ActionResult ExportData()
        {
            CommonController controller = new CommonController();
            Dictionary<string,string> title=new Dictionary<string,string>();
            title.Add("VehicleId", "车牌号");
            title.Add("Sim", "SIM卡号");
            title.Add("ParentUnitName", "所属单位");
            title.Add("StatisticsTime", "统计时间");
            title.Add("Mileage", "里程（公里）");
            string fileName = "里程统计";
            if (Session["SearchData"] != null)
            {
                return controller.ListToExcel<MileageStatistics>((Session["SearchData"] as IEnumerable<MileageStatistics>).ToList(), title, fileName,Server);
            }
            else
            {
                ViewBag.Message = "数据已过期，请重新查询";
                return View("ActionResult");
            }
        }
    }
}
